﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Widgetsphere.Generator.Models;
using Widgetsphere.Generator.ProjectItemGenerators;
using System.Xml;
using Widgetsphere.Generator.Common.Util;
using Widgetsphere.Generator.Installer.ProjectItemGenerators;

namespace Widgetsphere.Generator.Installer
{
	internal static class SQLHelper
	{
		#region GetModelDifferenceSQL

		public static string GetModelDifferenceSQL(ModelRoot modelOld, ModelRoot modelNew)
		{
			var sb = new StringBuilder();

			#region Loop and DELETE tables
			foreach (var oldT in modelOld.Database.Tables.Where(x => x.Generated))
			{
				var newT = modelNew.Database.Tables.Where(x => x.Generated && x.Key.ToLower() == oldT.Key.ToLower()).FirstOrDefault();
				if (newT == null)
				{
					//DELETE TABLE
					sb.AppendLine(SQLGeneration.GetSQLDropTable(oldT));
					sb.AppendLine("GO");
					sb.AppendLine();
				}
				else if (newT != null && oldT.AllowAuditTracking && !newT.AllowAuditTracking)
				{
					//If the old model had audit tracking and the new one does not, add a TODO in the script
					var tableName = "__AUDIT__" + Globals.GetTableDatabaseName(modelOld, oldT);
					sb.AppendLine("--TODO: REMOVE AUDIT TABLE '" + tableName + "'");
					sb.AppendLine("--The previous model had audit tracking turn on for table '" + Globals.GetTableDatabaseName(modelOld, oldT) + "' and now it is turned off.");
					sb.AppendLine("--The audit table will not be removed automatically. If you want to remove it, uncomment the following script.");
					sb.AppendLine("--DROP TABLE [" + tableName + "]");
					sb.AppendLine("--GO");
					sb.AppendLine();
				}
				//else if (tList[0].DatabaseName != oldT.DatabaseName)
				//{
				//  //RENAME TABLE
				//  sb.AppendLine("if exists(select * from sysobjects where name = '" + oldT.DatabaseName + "' and xtype = 'U')");
				//  sb.AppendLine("exec sp_rename [" + oldT.DatabaseName + "], [" + tList[0].DatabaseName + "]");
				//}
			}
			#endregion

			#region Loop and Modify tables
			foreach (var newT in modelNew.Database.Tables.OrderBy(x => x.Name))
			{
				var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault();
				if (oldT != null)
				{
					var querylist = new List<string>();

					#region Rename table if need be
					if (oldT.DatabaseName != newT.DatabaseName)
					{
						//RENAME TABLE
						sb.AppendLine("--RENAME TABLE '" + oldT.DatabaseName + "' TO '" + newT.DatabaseName + "'");
						sb.AppendLine("if exists(select * from sysobjects where name = '" + oldT.DatabaseName + "' and xtype = 'U')");
						sb.AppendLine("exec sp_rename '" + oldT.DatabaseName + "', '" + newT.DatabaseName + "'");
						sb.AppendLine();

						if (newT.EnforcePrimaryKey)
						{
							//RENAME PRIMARY KEY (it will be readded in create script)
							var oldIndexName = "PK_" + oldT.DatabaseName.ToUpper();
							var newIndexName = "PK_" + newT.DatabaseName.ToUpper();
							sb.AppendLine("--RENAME PRIMARY KEY FOR TABLE '" + oldT.DatabaseName + "'");
							sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
							sb.AppendLine("exec sp_rename '" + oldIndexName + "', '" + newIndexName + "'");
							sb.AppendLine();
						}

						//rename all indexes for this table
						sb.AppendLine("--RENAME INDEX FOR TABLE '" + oldT.DatabaseName + "' TO '" + newT.DatabaseName + "'");
						foreach (Column column in newT.GetColumns())
						{
							var oldIndexName = "IDX_" + oldT.DatabaseName.ToUpper() + "_" + column.DatabaseName.ToUpper();
							var newIndexName = "IDX_" + newT.DatabaseName.ToUpper() + "_" + column.DatabaseName.ToUpper();
							sb.AppendLine("--RENAME INDEX FOR TABLE '" + oldT.DatabaseName + "'");
							sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
							sb.AppendLine("exec sp_rename @objname='" + newT.GetSQLSchema() + "." + newT.DatabaseName + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='INDEX'");
							sb.AppendLine();
						}

					}
					#endregion

					#region Delete Columns
					foreach (Reference oldRef in oldT.Columns)
					{
						Column oldC = (Column)oldRef.Object;
						Column newC = Globals.GetColumnByKey(newT.Columns, oldC.Key);
						if (newC == null)
						{
							//DELETE COLUMN
							sb.AppendLine(SQLGeneration.GetSQLDropColumn(modelNew, oldC));
							sb.AppendLine("GO");
							sb.AppendLine();
						}
						else if (newC.DatabaseName != oldC.DatabaseName)
						{
							////RENAME COLUMN
							//string sql = "if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + oldC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" +
							//             "AND not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + newC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" + Environment.NewLine +
							//             "EXEC sp_rename @objname = '" + newT.DatabaseName + "." + oldC.DatabaseName + "', @newname = '" + newC.DatabaseName + "', @objtype = 'COLUMN'";
							//if (!querylist.Contains(sql))
							//{
							//  querylist.Add(sql);
							//  sb.AppendLine(sql);
							//  sb.AppendLine("GO");
							//  sb.AppendLine();
							//}
						}

					}
					#endregion

					#region Add columns
					foreach (Reference newRef in newT.Columns)
					{
						Column newC = (Column)newRef.Object;
						Column oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
						if (oldC == null)
						{
							//ADD COLUMN
							sb.AppendLine(SQLGeneration.GetSQLAddColumn(newC));
							sb.AppendLine("GO");
							sb.AppendLine();
						}
						else if (newC.DatabaseName != oldC.DatabaseName)
						{
							//RENAME COLUMN
							string sql = "if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + oldC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "') " +
													 "AND not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + newC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" + Environment.NewLine +
													 "EXEC sp_rename @objname = '" + newT.DatabaseName + "." + oldC.DatabaseName + "', @newname = '" + newC.DatabaseName + "', @objtype = 'COLUMN'";
							//if (!querylist.Contains(sql))
							//{
							querylist.Add(sql);
							sb.AppendLine("--RENAME COLUMN '" + oldT.DatabaseName + "." + oldC.DatabaseName + "'" + Environment.NewLine);
							sb.AppendLine(sql);
							sb.AppendLine("GO");
							sb.AppendLine();
							//}

							var oldIndexName = "IDX_" + oldT.DatabaseName.ToUpper() + "_" + oldC.DatabaseName.ToUpper();
							var newIndexName = "IDX_" + newT.DatabaseName.ToUpper() + "_" + newC.DatabaseName.ToUpper();
							sb.AppendLine("--RENAME INDEX FOR COLUMN '" + newT.DatabaseName + "." + newC.DatabaseName + "'");
							sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
							sb.AppendLine("exec sp_rename @objname='" + newT.GetSQLSchema() + "." + newT.DatabaseName + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='INDEX'");
							sb.AppendLine("GO");
							sb.AppendLine();

						}

					}
					#endregion

					#region Modify Columns
					foreach (var newC in newT.GetColumns())
					{
						var oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
						if (oldC != null)
						{
							var document = new XmlDocument();
							document.LoadXml("<a></a>");
							var n1 = XmlHelper.AddElement(document.DocumentElement, "q");
							var n2 = XmlHelper.AddElement(document.DocumentElement, "q");
							oldC.XmlAppend(n1);
							newC.XmlAppend(n2);
							if (newC.CorePropertiesHash != oldC.CorePropertiesHash)
							{
								//MODIFY COLUMN
								sb.AppendLine(SQLGeneration.GetSQLModifyColumn(newC, oldC));
								sb.AppendLine("GO");
								sb.AppendLine();
							}
						}
					}
					#endregion

					#region Primary Key Changed

					//If the primary key changed, then generate a commented script that marks where the user can manually intervene
					var newPKHash = string.Join("|", newT.PrimaryKeyColumns.OrderBy(x => x.Name).Select(x => x.Name.ToLower()).ToArray());
					var oldPKHash = string.Join("|", oldT.PrimaryKeyColumns.OrderBy(x => x.Name).Select(x => x.Name.ToLower()).ToArray());
					if (newPKHash != oldPKHash)
					{
						sb.AppendLine("--GENERATION NOTE **");
						sb.AppendLine("--THE PRIMARY KEY HAS CHANGED, THIS MAY REQUIRE MANUAL INTERVENTION");
						sb.AppendLine("--THE FOLLOWING SCRIPT WILL DROP AND READD THE PRIMARY KEY HOWEVER IF THERE ARE RELATIONSHIPS");
						sb.AppendLine("--BASED ON THIS IT, THE SCRIPT WILL FAIL. YOU MUST DROP ALL RELATIONSHIPS FIRST.");

						string tableName = Globals.GetTableDatabaseName(modelNew, newT);
						string indexName = "PK_" + tableName;
						indexName = indexName.ToUpper();
						sb.AppendLine("--DROP PRIMARY KEY FOR TABLE [" + tableName + "]");
						sb.AppendLine("--if exists (select * from dbo.sysobjects where id = object_id(N'" + indexName + "'))");
						sb.AppendLine("--ALTER TABLE [" + newT.GetSQLSchema() + "].[" + tableName + "] DROP CONSTRAINT [" + indexName + "]");
						sb.AppendLine("--GO");
						sb.AppendLine();

						var sql = Globals.GetPrimaryKeyCreateScript(newT, modelNew);
						var lines = sql.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);

						//Comment the whole SQL block
						int index = 0;
						foreach (var s in lines)
						{
							var l = s;
							if (!l.StartsWith("--"))
								l = "--" + l;
							lines[index] = l;
							index++;
						}

						sb.Append(string.Join("\r\n", lines));
					}

					#endregion
				}
			}
			#endregion

			#region Loop and Add tables

			foreach (var newT in modelNew.Database.Tables.ToList())
			{
				var tList = modelOld.Database.Tables.GetByKey(newT.Key);
				if (tList.Length == 0)
				{
					//ADD TABLE
					sb.AppendLine("--CREATE TABLE [" + newT.DatabaseName + "]");
					sb.AppendLine(SQLGeneration.GetSQLCreateTable(modelNew, newT));
					sb.AppendLine("GO");
					sb.AppendLine();
				}
			}

			#endregion

			#region Move tables between schemas

			int reschema = 0;
			foreach (Table newT in modelNew.Database.Tables)
			{
				Table[] tList = modelOld.Database.Tables.GetByKey(newT.Key);
				if (tList.Length > 0)
				{
					Table oldT = tList[0];
					if (string.Compare(oldT.GetSQLSchema(), newT.GetSQLSchema(), true) != 0)
					{
						if (reschema == 0)
							sb.AppendLine("--MOVE TABLES TO PROPER SCHEMA IF NEED BE");
							
							//This table has changed schema so script it
						sb.AppendLine("--CREATE DATABASE SCHEMAS");
						sb.AppendLine("if not exists(select * from sys.schemas where name = '" + newT.GetSQLSchema() + "')");
						sb.AppendLine("exec('CREATE SCHEMA [" + newT.GetSQLSchema() + "]')");
						sb.AppendLine("GO");
						sb.AppendLine("if exists (select * from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id where t.name = '" + newT.DatabaseName + "' and s.name = '" + oldT.GetSQLSchema() + "')");
						sb.AppendLine("	ALTER SCHEMA [" + newT.GetSQLSchema() + "] TRANSFER [" + oldT.GetSQLSchema() + "].[" + newT.DatabaseName + "];");
						sb.AppendLine("GO");
						reschema++;
					}
				}
			}

			if (reschema > 0) sb.AppendLine();

			#endregion

			return sb.ToString();
		}

		#endregion

		#region GetSQLSchema

		public static string GetSQLSchema(this Table item)
		{
			if (string.IsNullOrEmpty(item.DBSchema)) return "dbo";
			return item.DBSchema;
		}

		public static string GetSQLSchema(this TableComponent item)
		{
			if (string.IsNullOrEmpty(item.Parent.DBSchema)) return "dbo";
			return item.Parent.DBSchema;
		}

		public static string GetSQLSchema(this CustomStoredProcedure item)
		{
			if (string.IsNullOrEmpty(item.DBSchema)) return "dbo";
			return item.DBSchema;
		}

		public static string GetSQLSchema(this CustomView item)
		{
			if (string.IsNullOrEmpty(item.DBSchema)) return "dbo";
			return item.DBSchema;
		}

		#endregion

	}
}

